CREATE PROC SP_CHECK_USER
@EMAIL_ID VARCHAR(100),
@PASSWORD VARCHAR(100)
AS
SELECT USER_ID,FIRST_NAME,LAST_NAME,ROLE_NAME,GENDER FROM MAS_LOGIN A 
JOIN MAS_ROLES B 
ON		A.FK_ROLE_ID = B.ROLE_ID 
	AND A.EMAIL_ID = @EMAIL_ID 
	AND A.PASSWORD = @PASSWORD
GO

CREATE PROC SP_GET_CATEGORIES
AS
SELECT	 CATEGORY_ID
		,CATEGORY_NAME
		,(SELECT FIRST_NAME + ' ' + LAST_NAME FROM MAS_LOGIN WHERE USER_ID = A.FK_CREATED_USER) CREATED_USER
		,(SELECT FIRST_NAME + ' ' + LAST_NAME FROM MAS_LOGIN WHERE USER_ID = A.FK_MODIFIED_USER) MODIFIED_USER
FROM MAS_CATEGORY A WHERE A.PARENT_CATEGORY = 0
GO

CREATE PROC SP_GET_SUB_CATEGORIES
@PARENT_CATEGORY VARCHAR(10)
AS
IF(@PARENT_CATEGORY = '0')
	BEGIN
		SELECT	 CATEGORY_ID
				,CATEGORY_NAME
				,(SELECT FIRST_NAME + ' ' + LAST_NAME FROM MAS_LOGIN WHERE USER_ID = A.FK_CREATED_USER) CREATED_USER
				,(SELECT FIRST_NAME + ' ' + LAST_NAME FROM MAS_LOGIN WHERE USER_ID = A.FK_MODIFIED_USER) MODIFIED_USER
		FROM MAS_CATEGORY A WHERE A.PARENT_CATEGORY <> 0
	END
ELSE
	BEGIN
		SELECT	 CATEGORY_ID
				,CATEGORY_NAME
				,(SELECT FIRST_NAME + ' ' + LAST_NAME FROM MAS_LOGIN WHERE USER_ID = A.FK_CREATED_USER) CREATED_USER
				,(SELECT FIRST_NAME + ' ' + LAST_NAME FROM MAS_LOGIN WHERE USER_ID = A.FK_MODIFIED_USER) MODIFIED_USER
		FROM MAS_CATEGORY A WHERE A.PARENT_CATEGORY = @PARENT_CATEGORY
	END
GO

CREATE PROC SP_INSERT_CATEGORY
@CATEGORY_NAME VARCHAR(100),
@PARENT_CATEGORY BIGINT,
@USER_ID BIGINT
AS
INSERT INTO MAS_CATEGORY
			(
				 CATEGORY_NAME
				,PARENT_CATEGORY
				,FK_CREATED_USER
				,FK_MODIFIED_USER
			)
VALUES (@CATEGORY_NAME,@PARENT_CATEGORY,@USER_ID,@USER_ID)
GO

CREATE PROC SP_UPDATE_CATEGORY
@CATEGORY_ID BIGINT,
@CATEGORY_NAME VARCHAR(100),
@PARENT_CATEGORY BIGINT,
@USER_ID BIGINT
AS
UPDATE MAS_CATEGORY SET  CATEGORY_NAME = @CATEGORY_NAME
						,PARENT_CATEGORY = @PARENT_CATEGORY
						,FK_MODIFIED_USER = @USER_ID
WHERE CATEGORY_ID = @CATEGORY_ID
GO

CREATE PROC SP_DELETE_CATEGORY
@CATEGORY_ID BIGINT
AS
DELETE MAS_CATEGORY 
WHERE CATEGORY_ID = @CATEGORY_ID
GO

CREATE PROC SP_INSERT_QUESTION
@QUESTION_TEXT VARCHAR(MAX),
@IMAGE_PATH VARCHAR(MAX),
@CATEGORY_ID BIGINT,
@USER_ID BIGINT
AS
INSERT INTO MAS_QUESTIONS 
		(
			 QUESTION_TEXT
			,IMAGE_URL
			,FK_CTEGORY_ID
			,FK_CREATED_USER
			,FK_MODIFIED_USER
		)
VALUES 
		(
			 @QUESTION_TEXT
			,@IMAGE_PATH
			,@CATEGORY_ID
			,@USER_ID
			,@USER_ID
		)
GO

CREATE PROC SP_GET_ALL_QUESTIONS
@CATEGORY_ID VARCHAR(100)
AS
IF(@CATEGORY_ID = '0')
	BEGIN
		SELECT	 QUESTION_ID
				,ROW_NUMBER() OVER (ORDER BY A.QUESTION_ID)ROW
				,QUESTION_TEXT
				,IMAGE_URL
				,FK_CTEGORY_ID
				,(SELECT FIRST_NAME + ' ' + LAST_NAME FROM MAS_LOGIN WHERE USER_ID = A.FK_CREATED_USER) CREATED_USER
				,(SELECT FIRST_NAME + ' ' + LAST_NAME FROM MAS_LOGIN WHERE USER_ID = A.FK_MODIFIED_USER) MODIFIED_USER
		FROM MAS_QUESTIONS A 
	END
ELSE
	BEGIN
		SELECT	 QUESTION_ID
				,ROW_NUMBER() OVER (ORDER BY A.QUESTION_ID)ROW
				,QUESTION_TEXT
				,IMAGE_URL
				,FK_CTEGORY_ID
				,(SELECT FIRST_NAME + ' ' + LAST_NAME FROM MAS_LOGIN WHERE USER_ID = A.FK_CREATED_USER) CREATED_USER
				,(SELECT FIRST_NAME + ' ' + LAST_NAME FROM MAS_LOGIN WHERE USER_ID = A.FK_MODIFIED_USER) MODIFIED_USER
		FROM MAS_QUESTIONS A WHERE A.FK_CTEGORY_ID = @CATEGORY_ID
	END
GO

ALTER PROC SP_GET_ANSWER
@QUESTION_ID BIGINT
AS
;WITH CTE
AS
(
SELECT	 ROW_NUMBER() OVER (ORDER BY A.ANSWE_ID)ROW
		,ANSWE_ID
		,ANSWER_TEXT
		,FK_QUESTION_ID
		,IS_CORRECT
		,(SELECT FIRST_NAME + ' ' + LAST_NAME FROM MAS_LOGIN WHERE USER_ID = A.FK_CREATED_USER) CREATED_USER
		,(SELECT FIRST_NAME + ' ' + LAST_NAME FROM MAS_LOGIN WHERE USER_ID = A.FK_MODIFIED_USER) MODIFIED_USER
FROM MAS_ANSWERS A WHERE A.FK_QUESTION_ID = @QUESTION_ID
)
SELECT A.CHARCTER,B.* FROM MAS_ALPHA_NUM A JOIN CTE B ON A.NUM = B.ROW
GO

ALTER PROC SP_UPDATE_QUESTION
@QUESTION_ID BIGINT,
@QUESTION_TEXT VARCHAR(MAX),
@CATEGORY_ID BIGINT,
@USER_ID BIGINT
AS
UPDATE MAS_QUESTIONS
	SET  QUESTION_TEXT = @QUESTION_TEXT
		,FK_CTEGORY_ID = CASE WHEN @CATEGORY_ID = 0 THEN FK_CTEGORY_ID ELSE @CATEGORY_ID END
		,FK_MODIFIED_USER = @USER_ID
WHERE QUESTION_ID = @QUESTION_ID
GO

CREATE PROC SP_DELETE_QUESTION
@QUESTION_ID BIGINT
AS
DELETE MAS_ANSWERS
WHERE FK_QUESTION_ID = @QUESTION_ID
DELETE MAS_QUESTIONS
WHERE QUESTION_ID = @QUESTION_ID
GO

CREATE PROC SP_INSERT_ANSWER
@ANSWET_TEXT VARCHAR(MAX),
@QUESTION_ID BIGINT,
@IS_CORRECT BIT,
@USER_ID BIGINT
AS
INSERT INTO MAS_ANSWERS
		(
			 ANSWER_TEXT
			,FK_QUESTION_ID
			,IS_CORRECT
			,FK_CREATED_USER
			,FK_MODIFIED_USER
		)
VALUES	(
			 @ANSWET_TEXT
			,@QUESTION_ID
			,@IS_CORRECT
			,@USER_ID
			,@USER_ID
		)
GO

CREATE PROC SP_UPDATE_ANSWER
@ANSWER_ID BIGINT,
@ANSWET_TEXT VARCHAR(MAX),
@IS_CORRECT BIT,
@USER_ID BIGINT
AS
IF(@IS_CORRECT = '1')
	BEGIN
DECLARE @QUESTION_ID BIGINT = (SELECT FK_QUESTION_ID FROM MAS_ANSWERS WHERE ANSWE_ID = @ANSWER_ID)
UPDATE MAS_ANSWERS SET IS_CORRECT = '0' WHERE FK_QUESTION_ID = @QUESTION_ID
	END
UPDATE MAS_ANSWERS
	SET		 ANSWER_TEXT = @ANSWET_TEXT
			,IS_CORRECT = @IS_CORRECT
			,FK_MODIFIED_USER = @USER_ID
	WHERE ANSWE_ID = @ANSWER_ID
GO

CREATE PROC SP_DELETE_ANSWER
@ANSWER_ID BIGINT
AS
DELETE MAS_ANSWERS WHERE ANSWE_ID = @ANSWER_ID
GO

ALTER PROC SP_INSERT_LOGIN
@EMAIL_ID VARCHAR(100),
@PASSWORD VARCHAR(100),
@FIRST_NAME VARCHAR(100),
@LAST_NAME VARCHAR(100),
@GENDER CHAR(1),
@FK_ROLE_ID VARCHAR(100)
AS
INSERT INTO MAS_LOGIN
		(
			 EMAIL_ID
			,PASSWORD
			,FIRST_NAME
			,LAST_NAME
			,GENDER
			,FK_ROLE_ID
		)
VALUES
		(
			 @EMAIL_ID
			,@PASSWORD
			,@FIRST_NAME
			,@LAST_NAME
			,@GENDER
			,(SELECT ROLE_ID FROM MAS_ROLES WHERE ROLE_NAME = @FK_ROLE_ID)
		)

SELECT USER_ID,FIRST_NAME,LAST_NAME,ROLE_NAME,GENDER FROM MAS_LOGIN A 
JOIN MAS_ROLES B 
ON		A.FK_ROLE_ID = B.ROLE_ID 
	AND A.USER_ID = SCOPE_IDENTITY()
GO

--CREATE PROC SP_INSERT_TESTS
--@TEST_NAME VARCHAR(1000),
--@NO_OF_QUESTIONS INT,
--@SHOW_SOLUTIONS BIT,
--@TIME_OFFSET DATETIME,
--@USER_ID BIGINT,
--@DIF_ID BIGINT,
--@CATEGORY_ID BIGINT,
--@EXPIRES DATETIME
--AS
--INSERT INTO TESTS
--		(
--			 TEST_NAME
--			,NO_OF_QUESTIONS
--			,SHOW_SOLUTIONS
--			,TIME_OFFSET
--			,FK_USER_ID
--			,FK_DIF_ID
--			,FK_CATEGORY_ID
--			,EXPIRES
--		)
--VALUES	(
--			 @TEST_NAME
--			,@NO_OF_QUESTIONS
--			,@SHOW_SOLUTIONS
--			,@TIME_OFFSET
--			,@USER_ID
--			,@DIF_ID
--			,@CATEGORY_ID
--			,@EXPIRES
--		)
--GO

CREATE PROC SP_INSERT_TESTS
@TEST_NAME VARCHAR(1000)
AS
INSERT INTO TESTS
		(
			 TEST_NAME
		)
VALUES	(
			 @TEST_NAME
		)
GO


SELECT * FROM TESTS
SELECT * FROM USER_TESTS
SELECT * FROM MAS_DIF_LEVEL




















